﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Collections.Generic;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using Jungle.Code.DAL;

namespace Jungle.Prodamo.PriceManager
{
    public class Price
    {
        #region Fileds
        private int priceId;
        private int userId;
        private int categoryId;
        private string code;
        private int producerId;
        private int modelId;
        private string description;
        private int guarant;
        private double priceGrnRozn;
        private double priceGrnOpt;
        private double priceUeRozn;
        private double priceUeOpt;
        private int have; // 0 - есть на складе, 1 - нет, 2 - заказ
        private DateTime pubDate;
        private bool vip;
        #endregion

        public List<Price> PriceList;
        public List<Category> Cats;

        #region Propert
        public int PriceId { get { return priceId; } set { priceId = value; } }
        public int UserId { get { return userId; } set { userId = value; } }
        public int CategoryId { get { return categoryId; } set { categoryId = value; } }
        public string Code { get { return code; } set { code = value; } }
        public int ProducerId { get { return producerId; } set { producerId = value; } }
        public int ModelId { get { return modelId; } set { modelId = value; } }
        public string Description { get { return description; } set { description = value; } }
        public int Guarant { get { return guarant; } set { guarant = value; } }
        public double PriceGrnRozn { get { return priceGrnRozn; } set { priceGrnRozn = value; } }
        public double PriceGrnOpt { get { return priceGrnOpt; } set { priceGrnOpt = value; } }
        public double PriceUeRozn { get { return priceUeRozn; } set { priceUeRozn = value; } }
        public double PriceUeOpt { get { return priceUeOpt; } set { priceUeOpt = value; } }
        public int Have { get { return have; } set { have = value; } }
        public DateTime PubDate { get { return pubDate; } set { pubDate = value; } }
        public bool Vip { get { return vip; } set { vip = value; } }
        #endregion

        public void Add()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Price_Add", connect);
                SqlDataReader reader = null;

                command.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
                command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = categoryId;
                command.Parameters.Add("@Code", SqlDbType.NChar, 20).Value = code;
                command.Parameters.Add("@ProducerId", SqlDbType.Int).Value = producerId;
                command.Parameters.Add("@ModelId", SqlDbType.Int).Value = modelId;
                command.Parameters.Add("@Description", SqlDbType.Text).Value = description;
                command.Parameters.Add("@Guarant", SqlDbType.Int).Value = guarant;
                command.Parameters.Add("@PriceGrnRozn", SqlDbType.Float).Value = priceGrnRozn;
                command.Parameters.Add("@PriceGrnOpt", SqlDbType.Float).Value = priceGrnOpt;
                command.Parameters.Add("@PriceUeRozn", SqlDbType.Float).Value = priceUeRozn;
                command.Parameters.Add("@PriceUeOpt", SqlDbType.Float).Value = priceUeOpt;
                command.Parameters.Add("@Have", SqlDbType.Int).Value = have;
                command.Parameters.Add("@Vip", SqlDbType.Bit).Value = vip;

                command.CommandType = CommandType.StoredProcedure;
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    priceId = Convert.ToInt32(reader.GetValue(0));
                }
                reader.Close();
                command.Connection.Close();

            }
        }
        public void Delete()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Price_Delete", connect);
                command.Parameters.Add("@PriceId", SqlDbType.Int).Value = priceId;
                command.CommandType = CommandType.StoredProcedure;
                
                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }
        }
        public void Update()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Price_Update", connect);

                command.Parameters.Add("@PriceId", SqlDbType.Int).Value = priceId;
                command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = categoryId;
                command.Parameters.Add("@ProducerId", SqlDbType.Int).Value = producerId;
                command.Parameters.Add("@ModelId", SqlDbType.Int).Value = modelId;
                command.Parameters.Add("@Description", SqlDbType.Text).Value = description;
                command.Parameters.Add("@Guarant", SqlDbType.Int).Value = guarant;
                command.Parameters.Add("@PriceGrnRozn", SqlDbType.Float).Value = priceGrnRozn;
                command.Parameters.Add("@PriceGrnOpt", SqlDbType.Float).Value = priceGrnOpt;
                command.Parameters.Add("@PriceUeRozn", SqlDbType.Float).Value = priceUeRozn;
                command.Parameters.Add("@PriceUeOpt", SqlDbType.Float).Value = priceUeOpt;
                command.Parameters.Add("@Have", SqlDbType.Int).Value = have;
                command.Parameters.Add("@Vip", SqlDbType.Bit).Value = vip;
                
                command.CommandType = CommandType.StoredProcedure;

                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }            
        }
        public void GetById()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Price_GetById", connect);
                SqlDataReader reader = null;

                command.Parameters.Add("@PriceId", SqlDbType.Int).Value = priceId;
                command.CommandType = CommandType.StoredProcedure;

                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    priceId = (int)reader["PriceId"];
                    userId = (int)reader["UserId"];
                    categoryId = (int)reader["CategoryId"];
                    code = reader["Code"].ToString().Trim();
                    producerId = (int)reader["ProducerId"];
                    modelId = (int)reader["ModelId"];
                    description = reader["Description"].ToString().Trim();
                    guarant = (int)reader["Guarant"];
                    priceGrnRozn = (double)reader["PriceGrnRozn"];
                    priceGrnOpt = (double)reader["PriceGrnOpt"];
                    priceUeRozn = (double)reader["PriceUeRozn"];
                    priceUeOpt = (double)reader["PriceUeOpt"];
                    have = (int)reader["Have"];
                    pubDate = (DateTime)reader["PubDate"];
                    vip = (bool)reader["Vip"];

                }
                reader.Close();
                command.Connection.Close();
            }
        }
        public void GetByUser()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Price_GetByUser", connect);
                SqlDataReader reader = null;

                Cats = new List<Category>(); 
                command.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
                command.CommandType = CommandType.StoredProcedure;

                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cat = new Category();
                    cat.CategoryId = (int)reader["CategoryId"];
                    cat.GetById();

                    Cats.Add(cat);
                }
                reader.Close();
                command.Connection.Close();
            }        
        }
        public void GetByProducer()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Price_GetByProducer", connect);
                SqlDataReader reader = null;

                PriceList = new List<Price>();
                command.Parameters.Add("@ProducerId", SqlDbType.Int).Value = producerId;
                command.CommandType = CommandType.StoredProcedure;

                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Price pr = new Price();
                    pr.priceId = (int)reader["PriceId"];
                    pr.GetById();

                    PriceList.Add(pr);
                }
                reader.Close();
                command.Connection.Close();
            }
        }
        public void GetByModel()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Price_GetByModel", connect);
                SqlDataReader reader = null;

                PriceList = new List<Price>();
                command.Parameters.Add("@ModelId", SqlDbType.Int).Value = modelId;
                command.CommandType = CommandType.StoredProcedure;

                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Price pr = new Price();
                    pr.priceId = (int)reader["PriceId"];
                    pr.GetById();

                    PriceList.Add(pr);
                }
                reader.Close();
                command.Connection.Close();
            }
        }
        public void GetByCategory()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Price_GetByCategory", connect);
                SqlDataReader reader = null;

                PriceList = new List<Price>();
                command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = categoryId;
                command.CommandType = CommandType.StoredProcedure;

                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Price pr = new Price();
                    pr.priceId = (int)reader["PriceId"];
                    pr.GetById();

                    PriceList.Add(pr);
                }
                reader.Close();
                command.Connection.Close();
            }
        }
        public void GetFresh()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Price_GetFresh", connect);
                SqlDataReader reader = null;

                PriceList = new List<Price>();
                command.CommandType = CommandType.StoredProcedure;

                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Price pr = new Price();
                    pr.priceId = (int)reader["PriceId"];
                    pr.GetById();

                    PriceList.Add(pr);
                }
                reader.Close();
                command.Connection.Close();
            }        
        }

        public int PriceCount()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Price_GetCountByUser", connect);
                SqlDataReader reader = null;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;

                command.Connection.Open();
                reader = command.ExecuteReader();
                {
                    if (reader.Read())
                    {
                        return Convert.ToInt32(reader.GetValue(0));
                    }
                    else return 0;
                }
                reader.Close();
                command.Connection.Close();
            }
        }
    }
}